/********************************************************************
FileName    :   Dal_sptp.cs
CreateTime  :   2015-10-03 02:10
BaseClass   :   Dal_sptp
FileType    :   .cs
Author      :  minjie
Description :   Dal_sptpDAL层（简单三层）
*********************************************************************/

using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Data;
namespace OS.DAL
{
    /// <summary>
    /// Dal_sptp的摘要说明。
    /// </summary>
    public partial class Dal_sptp
    {

        public bool Exists(string spbh)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from sptp");
            strSql.Append(" where spbh=@spbh ");
            SqlParameter[] parameters = { new SqlParameter("@spbh", SqlDbType.VarChar, 50) };
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary> 
        /// 增加一条数据 
        /// </summary> 
        public bool Add(sptp_Entity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into sptp(");
            strSql.Append("spbh,tp) values (");
            strSql.Append("	@spbh,@tp	 ) ");
            SqlParameter[] parameters = { 
              new SqlParameter("@spbh", SqlDbType.Char,10) ,              
              new SqlParameter("@tp", SqlDbType.Image)};
            if (model.spbh != null)
                parameters[0].Value = model.spbh;
            else
                parameters[0].Value = DBNull.Value;

            if (model.tp != null)
                parameters[1].Value = model.tp;
            else
                parameters[1].Value = DBNull.Value;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false; ;
            }
        }

        /// <summary> 
        /// 增加一条数据(字符串) 
        /// </summary> 
        public string AddStr(sptp_Entity model)
        {
            if (model.spbh == null)
            {
                model.spbh = "";
            }


            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into sptp(");
            strSql.Append("spbh,tp) values (");
            strSql.Append("	'" + model.spbh + "','" + model.tp + "'	 ) ");
            return strSql.ToString();
        }


        /// <summary> 
        /// 更新一条数据 
        /// </summary> 
        public bool Update(sptp_Entity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update sptp set ");
            strSql.Append(" spbh = @spbh,");
            strSql.Append(" tp = @tp");
            strSql.Append(" where  spbh=@spbh ");

            SqlParameter[] parameters = { 
              new SqlParameter("@spbh", SqlDbType.Char,10) ,              
              new SqlParameter("@tp", SqlDbType.Image)};
            if (model.spbh != null)
                parameters[0].Value = model.spbh;
            else
                parameters[0].Value = DBNull.Value;

            if (model.tp != null)
                parameters[1].Value = model.tp;
            else
                parameters[1].Value = DBNull.Value;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary> 
        /// 删除一条数据 
        /// </summary> 
        public bool Delete(string spbh)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from sptp ");
            strSql.Append(" where  spbh=@spbh");
            SqlParameter[] parameters = { new SqlParameter("@spbh", SqlDbType.Char, 10) };
            parameters[0].Value = spbh;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary> 
        /// 批量删除一批数据 
        /// </summary> 
        public bool DeleteList(string spbhlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from sptp ");
            strSql.Append(" where ID in (" + spbhlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary> 
        /// 得到一个对象实体 
        /// </summary> 
        public sptp_Entity GetModel(string spbh)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  spbh,tp");
            strSql.Append("  from sptp");
            strSql.Append(" where  spbh=@spbh ");
            SqlParameter[] parameters = { new SqlParameter("@spbh", SqlDbType.Char, 10) };
            parameters[0].Value = spbh;

            sptp_Entity model = new sptp_Entity();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                model.spbh = dt.Rows[0]["spbh"].ToString();
                if (dt.Rows[0]["tp"] != null && dt.Rows[0]["tp"].ToString() != "")
                {
                    model.tp = (byte[])dt.Rows[0]["tp"];
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary> 
        /// 分页获取数据列表 
        /// </summary> 
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.spbh desc");
            }
            strSql.Append(")AS Row, T.*  from sptp T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary> 
        /// 获得数据列表 
        /// </summary> 
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM sptp ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetNullTpList()
        {
          //  string sql = "select *from sptp  where tp is null or   DATALENGTH(tp) <100 ";
            string sql = "select *from cptph  where tp is null or   DATALENGTH(tp) <100 ";


            return DbHelperSQL.Query(sql);
        }

        /// <summary> 
        /// 获得前几行数据 
        /// </summary> 
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM sptp ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary> 
        /// 获得记录条数 
        /// </summary> 
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) ");
            strSql.Append(" FROM sptp ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null && obj.ToString() != "")
            {
                return Convert.ToInt32(obj);
            }
            else
                return 0;
        }


    }

}
